package com.gxjtkyy.datadistributer.writer.rdbms.service;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.gxjtkyy.datadistributer.common.exception.ColumnParseException;
import com.gxjtkyy.datadistributer.writer.rdbms.config.WriterConfig;
import com.gxjtkyy.datadistributer.writer.rdbms.constant.CommonConstant;
import com.gxjtkyy.datadistributer.writer.rdbms.util.SqlTemplate;
import com.gxjtkyy.datadistributer.writer.rdbms.util.TableColumnsUtil;
import com.gxjtkyy.datadistributer.writer.rdbms.util.oracle.MysqlSqlTemplate;
import com.gxjtkyy.datadistributer.writer.rdbms.util.oracle.OracleSqlTemplate;
import com.gxjtkyy.datadistributer.writer.rdbms.util.oracle.PostgreSqlTemplate;
import com.gxjtkyy.datadistributer.writer.rdbms.vo.DbBase;
import com.gxjtkyy.datadistributer.writer.rdbms.vo.DbOperation;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.transaction.annotation.Transactional;

public abstract class AbstractDbWriterService<T> {
   protected final JdbcTemplate jdbcTemplate;
   @Resource
   private WriterConfig writerConfig;
   @Autowired
   private TableColumnsUtil tableColumnsUtil;

   @Transactional(
      rollbackFor = {Exception.class}
   )
   public void writeDb(List<DbOperation> dbOperations) {
      Map<String, List<DbOperation>> collect = dbOperations.stream().collect(Collectors.groupingBy(DbOperation::getSql));
      collect.forEach((key, value) -> {
         List<Object[]> batchArgs = value.stream().map(DbOperation::getArgs).collect(Collectors.toList());
         this.jdbcTemplate.batchUpdate(key, batchArgs);
      });
   }

   private Object[] optimizeArgs(Object[] args) {
      for(int i = 0; i < args.length; ++i) {
         Object arg = args[i];
         if (arg instanceof String) {
            if ("oracle".equalsIgnoreCase(this.writerConfig.getTargetDbType())) {
               args[i] = new SqlParameterValue(999, arg);
            } else if ("mysql".equalsIgnoreCase(this.writerConfig.getTargetDbType())) {
               args[i] = new SqlParameterValue(CommonConstant.MYSQL_TYPE_CHAR, arg);
            } else if ("postgresql".equalsIgnoreCase(this.writerConfig.getTargetDbType())) {
//               args[i] = isNumeric(arg);
            }
         }
      }

      return args;
   }

   private Object isNumeric(Object object) {
      if (object == null) {
         return object;
      }
      if (StringUtils.isBlank(object.toString())) {
         return object;
      }
      String str = object.toString();
      if (str.matches("-?\\d+(\\.\\d+)?")) {
//         if (str.length() <=)
         return new BigDecimal(str);
      }
      return object;
   }

   public DbOperation generateInsertDbOperation(String originalRecord) {
      try {
         return this.generateInsertDbOperation(this.parseOriginalRecord(originalRecord));
      } catch (JsonProcessingException e) {
         // 可以选择抛出自定义异常或重新抛出原始异常
         throw new RuntimeException("Failed to pgenerateInsertDbOperation", e);
      }
   }

   public DbOperation generateInsertDbOperation(T dbRecord) {

      return this.isIgnored(dbRecord) ? null : this.generateDbOperation(dbRecord, (dbBase) -> {
         Map<String, String> columnMap = tableColumnsUtil.columnNameMap.get(dbBase.getTargetTableName());
         String insertSql = this.getSqlTemplate().getInsertSql((String)null, dbBase.getTargetTableName(), dbBase.getKeyColumnNames(), dbBase.getOtherColumnNames(), columnMap);
         List<Object> argList = new ArrayList();
         argList.addAll(dbBase.getKeyValues());
         argList.addAll(dbBase.getOtherValues());
         return DbOperation.of(insertSql, this.optimizeArgs(argList.toArray()), dbBase.isInsertable(), dbBase.isUpdatable(), dbBase.isDropWhenValueTooLarge(), dbBase.isDropWhenCannotInsertNull(), dbBase.isDropWhenValueLargeThanSpecifiedPrecision());
      });
   }

   public DbOperation generateSaveOrUpdateDbOperation(String originalRecord) {
      try {
         return this.generateSaveOrUpdateDbOperation(this.parseOriginalRecord(originalRecord));
      } catch (JsonProcessingException e) {
         throw new RuntimeException("Failed to generateMergeDbOperation", e);
      }
   }

   public DbOperation generateSaveOrUpdateDbOperation(T dbRecord) {
      return this.isIgnored(dbRecord) ? null : this.generateDbOperation(dbRecord, (dbBase) -> {
         Map<String, String> columnMap = tableColumnsUtil.columnNameMap.get(dbBase.getTargetTableName());
         String mergeSql = this.getSqlTemplate().getSaveOrUpdateSql((String)null, dbBase.getTargetTableName(), dbBase.getKeyColumnNames(), dbBase.getOtherColumnNames(), dbBase.getUpdateColumnNames(), dbBase.getComparableColumns(), dbBase.isInsertable(), dbBase.isUpdatable(), columnMap);
         List<Object> argList = new ArrayList();
         argList.addAll(dbBase.getKeyValues());
         argList.addAll(dbBase.getOtherValues());
         return DbOperation.of(mergeSql, this.optimizeArgs(argList.toArray()), dbBase.isInsertable(), dbBase.isUpdatable(), dbBase.isDropWhenValueTooLarge(), dbBase.isDropWhenCannotInsertNull(), dbBase.isDropWhenValueLargeThanSpecifiedPrecision());
      });
   }

   public DbOperation generateMergeDbOperation(String originalRecord) {
      try {
         return this.generateMergeDbOperation(this.parseOriginalRecord(originalRecord));
      } catch (JsonProcessingException e) {
         throw new RuntimeException("Failed to generateMergeDbOperation", e);
      }
   }

   public DbOperation generateInsertOrUpdateDbOperation(String originalRecord) throws JsonProcessingException {
      return this.generateInsertOrUpdateDbOperation(this.parseOriginalRecord(originalRecord));
   }

   /**按主键查询返回数量，判断是用更新还是插入*/
   public DbOperation generateInsertOrUpdateDbOperation(T dbRecord) {
      if (this.isIgnored(dbRecord)) {
         return null;
      } else {
         DbOperation dbOperation = this.generateDbOperation(dbRecord, (dbBase) -> {
            String countSql = this.getSqlTemplate().getCountSql((String)null, dbBase.getTargetTableName(), dbBase.getKeyColumnNames());
            return DbOperation.of(countSql, this.optimizeArgs(dbBase.getKeyValues().toArray(new Object[0])), dbBase.isInsertable(), dbBase.isUpdatable(), dbBase.isDropWhenValueTooLarge(), dbBase.isDropWhenCannotInsertNull(), dbBase.isDropWhenValueLargeThanSpecifiedPrecision());
         });
         Long count = (Long)this.jdbcTemplate.query(dbOperation.getSql(), (rs) -> {
            if (rs.next()) {
               return rs.getLong(1);
            } else {
               throw new ColumnParseException("count 异常");
            }
         }, dbOperation.getArgs());
         if (count == 0L) {
            return this.generateInsertDbOperation(dbRecord);
         } else if (count == 1L) {
            return dbOperation.isUpdatable() ? this.generateUpdateDbOperation(dbRecord) : null;
         } else {
            throw new ColumnParseException("按主键查询返回数量大于 1");
         }
      }
   }

   public DbOperation generateMergeDbOperation(T dbRecord) {
      return this.isIgnored(dbRecord) ? null : this.generateDbOperation(dbRecord, (dbBase) -> {
         Map<String, String> columnMap = tableColumnsUtil.columnNameMap.get(dbBase.getTargetTableName());
         String mergeSql = this.getSqlTemplate().getMergeSql((String)null, dbBase.getTargetTableName(), dbBase.getKeyColumnNames(), dbBase.getOtherColumnNames(), dbBase.getUpdateColumnNames(), dbBase.getComparableColumns(), dbBase.isInsertable(), dbBase.isUpdatable(), columnMap);
         List<Object> argList = new ArrayList();
         argList.addAll(dbBase.getKeyValues());
         argList.addAll(dbBase.getOtherValues());
         return DbOperation.of(mergeSql, this.optimizeArgs(argList.toArray()), dbBase.isInsertable(), dbBase.isUpdatable(), dbBase.isDropWhenValueTooLarge(), dbBase.isDropWhenCannotInsertNull(), dbBase.isDropWhenValueLargeThanSpecifiedPrecision());
      });
   }

   public DbOperation generateUpdateDbOperation(T dbRecord) {
      return this.isIgnored(dbRecord) ? null : this.generateDbOperation(dbRecord, (dbBase) -> {
         String updateSql = this.getSqlTemplate().getUpdateSql((String)null, dbBase.getTargetTableName(), dbBase.getKeyColumnNames(), dbBase.getUpdateColumnNames());
         List<Object> argList = new ArrayList();
         argList.addAll(dbBase.getUpdateValues());
         argList.addAll(dbBase.getKeyColumnNames());
         return DbOperation.of(updateSql, this.optimizeArgs(argList.toArray()), dbBase.isInsertable(), dbBase.isUpdatable(), dbBase.isDropWhenValueTooLarge(), dbBase.isDropWhenCannotInsertNull(), dbBase.isDropWhenValueLargeThanSpecifiedPrecision());
      });
   }

   public DbOperation generateDbOperation(T dbRecord, Function<DbBase, DbOperation> function) {
      return (DbOperation)function.apply(this.getDbBase(dbRecord));
   }

   private SqlTemplate getSqlTemplate() {
      if ("mysql".equalsIgnoreCase(this.writerConfig.getTargetDbType())) {
         return new MysqlSqlTemplate();
      } if ("postgresql".equalsIgnoreCase(this.writerConfig.getTargetDbType())) {
         return new PostgreSqlTemplate();
      } else {
         return "oracle".equalsIgnoreCase(this.writerConfig.getTargetDbType()) ? new OracleSqlTemplate() : new OracleSqlTemplate();
      }
   }

   public abstract DbBase getDbBase(T dbRecord);

   protected abstract boolean isIgnored(T dbRecord);

   protected abstract T parseOriginalRecord(String originalRecord) throws JsonProcessingException;

   public AbstractDbWriterService(final JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
   }
}
